package cl.mt.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;

import cl.mt.dto.DTOParametros;
import cl.mt.dto.ParametroDTO;
import cl.mt.util.bd.DatosTabla;
import cl.mt.util.bd.Parametro;

public class DataParametros extends DatosTabla {
	private static final String sqlGetId = "SELECT MAX(ID)+1 AS IDX FROM MASTRANSPORTE.MST_PARAMETROS_GENERALES";
	private static final String sqlFindByPK = "SELECT ID, DESCRIPCION, VALOR, VIGENTE, FECHA FROM MASTRANSPORTE.MST_PARAMETROS_GENERALES where ID=?";
	private static final String sqlFindByParameter = "SELECT ID, DESCRIPCION, VALOR, VIGENTE, FECHA FROM MASTRANSPORTE.MST_PARAMETROS_GENERALES where DESCRIPCION=?";
	private static final String sqlFindAll = "SELECT ID, DESCRIPCION, VALOR, VIGENTE, FECHA FROM MASTRANSPORTE.MST_PARAMETROS_GENERALES";
	private static final String sqlCreate = "INSERT INTO MASTRANSPORTE.MST_PARAMETROS_GENERALES (ID, DESCRIPCION, VALOR, VIGENTE, FECHA) VALUES (?, ?, ?, ?, ?)";
	private static final String sqlDeleteByPK = "DELETE FROM MASTRANSPORTE.MST_PARAMETROS_GENERALES where ID=?";
	private static final String sqlUpdate = "UPDATE MASTRANSPORTE.MST_PARAMETROS_GENERALES SET DESCRIPCION=?, VALOR=?, VIGENTE=?, FECHA=? where ID=?";
	private static final String sqlUpdateContador = "UPDATE MASTRANSPORTE.MST_PARAMETROS_GENERALES SET VALOR=? WHERE DESCRIPCION=?";


	//mc_parametros
	private final static String sqlFindAllMC = "SELECT * FROM MST.MC_PARAMETRO";
    private final static String sqlInsertMC = "INSERT INTO MST.MC_PARAMETRO(PARA_CODIGO, PARA_KEY, PARA_VALOR) VALUES (?, ?, ?)";

	
	private DTOParametros setDatosRegistro(ResultSet rs) throws SQLException {
		DTOParametros dto = new DTOParametros();
		dto.setId(formatInteger(rs.getObject("ID")));
		dto.setDescripcion(formatString(rs.getObject("DESCRIPCION")));
		dto.setValor(formatString(rs.getObject("VALOR")));
		dto.setVigente(formatString(rs.getObject("VIGENTE")));
		dto.setFecha(formatDate(rs.getDate("FECHA")));
		return dto;
	}

	public Integer generateId() {
		Parametro[] params = null;
		Integer id = null;
		try {
			getConnection();
			ResultSet rs = executeQuery(sqlGetId, params);
			while (rs.next()) {
				id = formatInteger(rs.getObject("IDX"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return id;
	}
	public DTOParametros findByPrimaryKey(Long id) {

		DTOParametros dto = new DTOParametros();
		Parametro[] params = new Parametro[] { getParametro(id, Types.INTEGER)};
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindByPK, params);
			while (rs.next()) {
				dto = setDatosRegistro( rs );
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return dto;
	}
	
	public DTOParametros findByPrimaryParameter(String descripcion) {

		DTOParametros dto = new DTOParametros();
		Parametro[] params = new Parametro[] { getParametro(descripcion, Types.VARCHAR)};
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindByParameter, params);
			while (rs.next()) {
				dto = setDatosRegistro( rs );
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return dto;
	}

	public Collection<DTOParametros> findAll() {

		Parametro[] params = null;
		Collection<DTOParametros> col = new LinkedList<DTOParametros>();
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindAll, params);
			while (rs.next()) {
				DTOParametros dto = setDatosRegistro( rs );
				col.add( dto );
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return col;
	}

	
	
	public int updateContador(DTOParametros dto) {

		Parametro[] params = new Parametro[] { getParametro(dto.getValor(), Types.VARCHAR),getParametro(dto.getDescripcion(), Types.VARCHAR)};
		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlUpdateContador, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}
	public int deleteByPrimaryKey(Integer id) {

		Parametro[] params = new Parametro[] { getParametro(id, Types.INTEGER)};
		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlDeleteByPK, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}

	public int create(DTOParametros dto) {

		Parametro[] params = new Parametro[] {
			getParametro(dto.getId(), Types.INTEGER),
			getParametro(dto.getDescripcion(), Types.VARCHAR),
			getParametro(dto.getValor(), Types.VARCHAR),
			getParametro(dto.getVigente(), Types.VARCHAR),
			getParametro(formatTimestamp(dto.getFecha()), Types.TIMESTAMP)
		};

		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlCreate, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}

	public int update(DTOParametros dto) {

		Parametro[] params = new Parametro[] {
			getParametro(dto.getDescripcion(), Types.VARCHAR),
			getParametro(dto.getValor(), Types.VARCHAR),
			getParametro(dto.getVigente(), Types.VARCHAR),
			getParametro(formatTimestamp(dto.getFecha()), Types.TIMESTAMP),
			getParametro(dto.getId(), Types.INTEGER)
		};

		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlUpdate, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}


	/**
	 *  MC
	 * @return
	 */
    public Collection<ParametroDTO> findAllMC() {

        Parametro[] params = null;
        Collection<ParametroDTO> col = new ArrayList<ParametroDTO>();
        try {
            getConnection();

            ResultSet rs = executeQuery(sqlFindAllMC, params);
            while (rs.next()) {
                ParametroDTO dto = new ParametroDTO();
                dto.setCodigo(formatString(rs.getObject("PARA_CODIGO")));
                dto.setKey(formatString(rs.getObject("PARA_KEY")));
                dto.setValor(formatString(rs.getObject("PARA_VALOR")));
                col.add(dto);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return col;
    }

    public int createMC(ParametroDTO dto) {

        Parametro[] params = new Parametro[]{getParametro(dto.getCodigo(), Types.VARCHAR), getParametro(dto.getKey(), Types.VARCHAR), getParametro(dto.getValor(), Types.VARCHAR)};

        int res = 0;

        try {
            getConnection();

            res = executeUpdate(sqlInsertMC, params);

            closeConnection();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return res;
    }

}

